package com.yehun.utils;


import jxl.CellView;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 导出Excel文件
 */
public class ExcelUtils {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    private OutputStream stream; //输出流
    private WritableWorkbook workbook;
    private WritableSheet sheet;
    private WritableCellFormat cellFormatTitle; //大标题样式
    private WritableCellFormat cellFormatHead; //小标题样式
    private WritableCellFormat cellFormatBody; //正文样式
    private String title; //标题
    private String[] headers; //小标题
    private Integer[] cellWidths; //列宽度

    private int cellAveWidth = 1200; //每个字符宽度
    private int cellMinWidth = 1000; //最小宽度
    private int cellMaxWidth = 10000; //最大宽度
    private int cellDateWidth = 5000; //时间格式宽度
    private int row = 0; //当前行数
    private SimpleDateFormat dateFormat; //时间类型格式化

    public void setDateFormat(SimpleDateFormat dateFormat) {
        this.dateFormat = dateFormat;
    }


    public ExcelUtils(String title, String[] headers, String filename) {
        this.title = title;
        this.headers = headers;
        this.cellWidths = new Integer[headers.length];
        for (int i = 0; i < headers.length; i++) this.cellWidths[i] = this.cellMinWidth;
        this.stream = this.getFile(filename);
        initialize();
    }

    public ExcelUtils(String title, String[] headers, OutputStream stream) {
        this.title = title;
        this.headers = headers;
        this.cellWidths = new Integer[headers.length];
        for (int i = 0; i < headers.length; i++) this.cellWidths[i] = this.cellMinWidth;
        this.stream = stream;
        initialize();
    }

    /**
     * 初始化
     */
    private void initialize() {
        try {
            // 大标题样式
            WritableFont fontTitle = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD); //字体
            this.cellFormatTitle = new WritableCellFormat(fontTitle);
            //this.cellFormatTitle.setBackground(Colour.IVORY);
            //this.cellFormatTitle.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
            this.cellFormatTitle.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            this.cellFormatTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            this.cellFormatTitle.setAlignment(Alignment.CENTRE); // 文字水平对齐
            this.cellFormatTitle.setWrap(false); // 文字是否换行
            // 小标题样式
            WritableFont fontHead = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);  //字体
            fontHead.setColour(Colour.WHITE);
            this.cellFormatHead = new WritableCellFormat(fontHead);
            this.cellFormatHead.setBackground(Colour.GRAY_50);
            this.cellFormatHead.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            this.cellFormatHead.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            this.cellFormatHead.setAlignment(Alignment.CENTRE); // 文字水平对齐
            this.cellFormatHead.setWrap(false); // 文字是否换行
            //正文样式
            WritableFont fontBody = new WritableFont(WritableFont.ARIAL, 10);  //字体
            this.cellFormatBody = new WritableCellFormat(fontBody);
            this.cellFormatBody.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            this.cellFormatBody.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            this.cellFormatBody.setAlignment(Alignment.CENTRE); // 文字水平对齐
            this.cellFormatBody.setWrap(false); // 文字是否换行

            this.workbook = Workbook.createWorkbook(stream);
            //创建工作表
            this.sheet = this.workbook.createSheet("Sheet", 0);
            this.sheet.setRowView(0, 800, false); //设置大标题行高
            this.sheet.addCell(new Label(0, 0, title, cellFormatBody));
            //设置纵横打印（默认为纵打）、打印纸
            SheetSettings sheetset = this.sheet.getSettings();
            sheetset.setProtected(false);

            this.setTitle(this.title);
            this.setHeaders(this.headers);

            if (this.dateFormat == null) {
                this.dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            }
        } catch (Exception e) {
            logger.error("ExcelUtils。initialize, Exception，", e);
        }
    }


    /**
     * 设置excel大标题
     */
    private void setTitle(String title) {
        if (this.title == null) {
            this.title = title;
        }

        if (this.title != null && !this.title.equals("")) {
            try {
                this.sheet.mergeCells(0, 0, this.headers.length - 1, 0);
                this.sheet.addCell(new Label(0, 0, this.title, this.cellFormatTitle));
            } catch (WriteException e) {
                logger.error("ExcelUtils.setTitle, WriteException,", e);
            }
            row++;
        }
    }

    /**
     * 设置标题
     */
    private void setHeaders(String[] headers) {
        if (this.headers == null) {
            this.headers = headers;
        }
        if (this.headers != null && this.headers.length > 1) {
            try {
                this.sheet.setRowView(row, 400, false); //设置行高
                for (int i = 0; i < this.headers.length; i++) {
                    this.sheet.addCell(new Label(i, this.row, this.headers[i], this.cellFormatHead));
                }
            } catch (WriteException e) {
                logger.error("ExcelUtils.setHeaders, WriteException,", e);
            }
            row++;
        }
    }

    //添加一行数据
    public void addRow(int rowNumber, Object[] cols) {
        try {
            this.sheet.setRowView(rowNumber + row, 400, false); //设置行高
            for (int i = 0; i < this.headers.length; i++) { // cell
                String content = "";
                if (i < cols.length) {
                    this.addCell(i, rowNumber, cols[i]);
                }
            }
        } catch (WriteException e) {
            logger.error("ExcelUtils.addRow, WriteException,", e);
        }

    }

    /**
     * 添加一列
     */
    private void addCell(int columeNumber, int rowNumber, Object object) throws WriteException {
        String content;
        if (object != null) {
            if (object instanceof String) {
                content = object.toString();
            } else if (object instanceof Integer) {
                content = object.toString();
            } else if (object instanceof Long) {
                content = object.toString();
            } else if (object instanceof Double) {
                content = String.format("%.2f", (Double) object);
            } else if (object instanceof Float) {
                content = String.format("%.2f", (Float) object);
            } else if (object instanceof BigDecimal) {
                content = String.format("%.2f", (BigDecimal) object);
            } else if (object instanceof Date) {
                content = dateFormat.format((Date) object);
            } else {
                content = object.getClass().getSimpleName();
            }
            if (object instanceof Date) { //时间类型固定宽度
                this.cellWidths[columeNumber] = this.cellDateWidth;
            } else {
                this.autoWidth(columeNumber, content);
            }
        } else {
            content = "";
        }
        this.sheet.addCell(new Label(columeNumber, rowNumber + row, content, this.cellFormatBody));
    }

    /**
     * 自动宽度
     */
    private void autoWidth(int columeNumber, String content) {
        int width = (int) (content.length() * 0.5 * this.cellAveWidth);
        if (width > this.cellWidths[columeNumber]) {
            this.cellWidths[columeNumber] = width > this.cellMaxWidth ? this.cellMaxWidth : width;
        }
    }

    /**
     * 设置response
     */
    public void setResponse(HttpServletResponse response, String filename) {
        response.reset();
        //response.setContentType("application/octet-stream; charset=utf-8");
        response.setContentType("application/msexcel; charset=utf-8");// 定义输出类型
        response.setHeader("Content-Disposition", "attachment; filename=" + EncodeUtils.urlEncode(filename));
    }

    /**
     * 输出到文件
     */
    public OutputStream getFile(String filename) {
        try {
            return new FileOutputStream(filename);
        } catch (FileNotFoundException e) {
            logger.error("ExcelUtils.getFile, FileNotFoundException,", e);
        }
        return null;
    }

    /**
     * 输出
     */
    public ExcelUtils write() {
        CellView navCellView = new CellView();
        for (int i = 0; i < this.headers.length; i++) {
            navCellView.setSize(this.cellWidths[i]);
            this.sheet.setColumnView(i, navCellView); //设置col显示样式
        }
        try {
            this.workbook.write();
        } catch (IOException e) {
            logger.error("ExcelUtils.write, IOException,", e);
        }
        return this;
    }

    /**
     * 释放资源
     */
    public void dispose() {
        try {
            this.workbook.close();
        } catch (IOException e) {
            logger.error("ExcelUtils.dispose, IOException,", e);
        } catch (WriteException e) {
            logger.error("ExcelUtils.dispose, WriteException,", e);
        }
    }

    /**
     * 导出测试
     */
    public static void main(String[] args) throws Throwable {
        logger.info(DateUtils.getFormat("yyyyMMddHHmm"));
        String[] headers = new String[10];
        for (int i = 0; i < 10; i++) {
            headers[i] = "表头" + i;
        }
        FileOutputStream stream = new FileOutputStream("/home/yehun/export.xlsx");
        ExcelUtils excelUtils = new ExcelUtils("表格标题", headers, stream);
        for (int i = 0; i < 100; i++) {
            Object[] cols = new Object[headers.length];
            for (int j = 0; j < headers.length; j++) {
                cols[j] = "数据" + j;
            }
            excelUtils.addRow(i, cols);
        }
        excelUtils.write().dispose();
        logger.debug("Export success.");
    }

}
